import re
from openpyxl import load_workbook
import copy
from .WordDB import WordSql
import sys
import os


class DbUse:
    def __int__(self):
        self.userid = 0
        self.wdsql = None
        self.intcol = []
        self.coursestileyuan = []
        self.usertbcol = []
        self.stname = []
        self.courses = []
        self.templatesname = []
        self.coursepath = ''

    def initdata(self):
        self.wdsql = WordSql('127.0.0.1', 'root', 'zx.123456', 3306, 'word', 'utf8')
        self.usertbcol = ['userid', 'name', 'password', 'major']
        self.dictuserinfo = {
            "userid": 0,
            "name": "",
            "password": "",
            "major": "",
        }
        self.stname = ['通识课', '通识选修课', '学科基础课', '学科专业基础课', '专业选修课', '集中性实践', '第二课堂']
        self.coursepath = "d:/word/excleapp/word/coursexlsx/"
        # tmpall = []
        # for i in wd.stname:
        #     sql = "describe {}".format(i)
        #     # print(sql)
        #     tmp = []
        #     for j in wd.wdsql.get_all(sql):
        #         tmp.extend(j[0:1])
        #     tmp = tmp[1:]
        #     tmpall.append(tmp)
        # self.coursestileyuan=tmpall
        self.templatesname = ['目标', '要求', '预期', '主干学科', '主要实践性教学环节', '专业核心课程']
        self.coursestileyuan = [
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '线上', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '线上', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业要求'),
            ('课程序号', '课程代码', '课程名称', '学时数', '学分数', '理论', '课内实践', '课外实践', '开课学期', '周学时', '考核方式', '备注', '毕业', '分组统计'),
            ('序号', '课程代码', '课程名称', '实践周数', '学分数', '学期安排', '考核方式', '备注', '毕业要求'),
            ('序号', '课程代码', '课程名称', '实践周数', '学分数', '学期安排', '考核方式', '备注')]
        self.intcol = [8, 8, 7, 7, 7, 3, 3]
        dbcoursestile = []
        for i in self.coursestileyuan:
            tmp = []
            for j in i:
                tmp.append(j)
            dbcoursestile.append(tmp[1:])
        self.coursestile = dbcoursestile

        self.createtbcourses(self.coursestileyuan)

    def LoadCourse(self, w=None, userid=0, username=None, userpassord=None, major=None):
        wd = load_workbook(w)
        # wd = load_workbook('template/湘南学院/计算机科学与技术/' + 'course.xlsx')
        # wd = load_workbook('template/湘南学院/物联网/' + 'course.xlsx')
        coursestile = []
        courses = []
        for ipage in range(len(wd.worksheets)):
            sheet = wd.worksheets[ipage]
            data_course = list(sheet.values)
            coursestile.append(data_course[0])
            courses.append(data_course[1:])
        self.coursestileyuan = coursestile
        dbcourses = []
        for i in courses:
            tmp = []
            for j in i:
                tmp.append(j[1:])
            dbcourses.append(tmp)
        self.courses = dbcourses
        # self.insertuser(userid, username, userpassord, major)
        # self.insertuser(userid, username, userpassord, major)
        # if self.selectmajorcourses(major) == -1:
        #     tbcourses = []
        #     for i in self.courses:
        #         tmp = []
        #         for j in i:
        #             tmpa = [major]
        #             for k in j:
        #                 tmpa.append(k)
        #             tmp.append(tmpa)
        #         tbcourses.append(tmp)
        #     print(tbcourses)
        #     self.insertcourse(tbcourses)
        # else:
        #     tbcourses = []
        #     for i in self.selectmajorcoursesyuan(major, courses):
        #         tmp = []
        #         for j in i:
        #             tmpa = [major]
        #             for k in j:
        #                 tmpa.append(k)
        #             tmp.append(tmpa)
        #         print(tmp)
        #         tbcourses.append(tmp)
        #     print(tbcourses)
        #     self.upcourses(tbcourses)

    def processingget_allresult(self, data):
        tmpall = []
        # for tmp in list(map(list, data)):
        for tmp in data:
            tmpall.extend(tmp)
        return tmpall

    def processingcoursestile(self, data):
        tmp = []
        for i in range(len(self.courses)):
            sqlinsert = "replace " + self.stname[i] + "(major," + "%s," * (len(self.coursestile[i]) - 1) + "%s)"
            sqlinsert = self.cur.mogrify(sqlinsert, self.coursestile[i])
            sqlinsert = re.sub('[\'"]', '', sqlinsert)
            sqlinsert += "values(" + "%s," * (len(data[i][0]) - 1) + "%s)"
            tmp.append(sqlinsert)
        return tmp

    # def insertcourse(self, data):
    #     for i in range(len(self.stname)):
    #         sqlinsert = "replace " + self.stname[i] + "(major," + "%s," * (len(self.coursestile[i]) - 1) + "%s)"
    #         self.wdsql.insertcoursers(sqlinsert, self.coursestile[i], data[i])

    def insertcourse(self, data):
        try:
            self.wdsql.connect()
            for i in range(len(self.stname)):
                sqlinsert = "replace " + self.stname[i] + "(major," + "%s," * (len(self.coursestile[i]) - 1) + "%s)"
                self.wdsql.insertcoursers(sqlinsert, self.coursestile[i], data[i])
            self.wdsql.conn.commit()
            self.wdsql.close()
        except Exception as result:
            self.wdsql.conn.rollback()
            print("课程插入失败,未知错误为%s", result)


    def inserMatrix(self,data,year):
        try:
            self.wdsql.connect()
            for i in data:
                conn = self.selectmatrix_again(year,i[0])
                if conn != -1:
                    sql_user = "update matrix set Content = '{Content}' where year = {year} and classname = '{classname}' and userid = {userid}".format(Content=i[1],year=year,classname=i[0],userid = self.userid)
                    self.wdsql.insert(sql_user)

                else:
                    sql_user = "replace matrix(classname,Content, year,userid)values('{classname}','{Content}',{year},{userid})".format(classname=i[0], Content=i[1], year=year,userid=self.userid)
                    self.wdsql.insert(sql_user)


        except Exception as result:

            self.wdsql.conn.rollback()

            print("课程插入失败,未知错误为%s", result)

    # def upcourses(self, data):
    #     for i in range(len(self.stname)):
    #         sqlinsert = "replace " + self.stname[i] + "(major," + "%s," * (len(self.coursestileyuan[i]) - 1) + "%s)"
    #         self.wdsql.insertcoursers(sqlinsert, self.coursestileyuan[i], data[i])
    def upcourses(self, data):
        try:
            self.wdsql.connect()
            for i in range(len(self.stname)):
                sqlinsert = "replace " + self.stname[i] + "(major," + "%s," * (len(self.coursestileyuan[i]) - 1) + "%s)"
                self.wdsql.insertcoursers(sqlinsert, self.coursestileyuan[i], data[i])
        except Exception as result:
            self.wdsql.conn.rollback()
            print("课程更新失败,未知错误为%s", result)

    def insertuser(self, userid, name, password, major):
        sql = "replace user(userid,name,password,major)values({},'{}','{}','{}')".format(userid, name, password, major)
        self.wdsql.insert(sql)

    def inserttemplates(self, major, data):
        templatesdictinfo = self.selecttemplatesdictinfo(major)
        print(major)
        print(templatesdictinfo)
        if templatesdictinfo == -1:
            sql = "replace templates (major,目标, 要求, 预期, 主干学科, 主要实践性教学环节, 专业核心课程)values('{major}'," \
                  "'{t}','{q}','{e}','{ms}','{st}','{mj}')".format(major=major, t=data[0], q=data[1], e=data[2],
                                                                   ms=data[3], st=data[4], mj=data[5])
            print(sql)
            self.wdsql.insert(sql)
        else:
            print(templatesdictinfo['templatesid'])
            sql = "replace templates (templatesid,major,目标, 要求, 预期, 主干学科, 主要实践性教学环节, 专业核心课程)values({templatesid},'{major}'," \
                  "'{t}','{q}','{e}','{ms}','{st}','{mj}')".format(templatesid=templatesdictinfo['templatesid'],
                                                                   major=major, t=data[0],
                                                                   q=data[1], e=data[2], ms=data[3], st=data[4],
                                                                   mj=data[5])
            self.wdsql.insert(sql)

    def insertcoursetemplates(self,year):
        userinfo = self.selectuserdictinfo(self.userid)
        coursetemplatedictinfo = self.selectcoursetemplates(year)
        # coursepath = self.coursepath + str(userinfo.get("userid")) + '_' + str(year) + userinfo.get("major") + "course.xlsx"
        # print(coursepath,'coursepath')
        if coursetemplatedictinfo != -1:
            year = int(year)
            coursepath = self.coursepath + str(userinfo.get("userid")) + '_' + str(year) + userinfo.get("major") + "course"+".xlsx"
            wordpath = "d:/word/excleapp/word/template/湘南学院/计算机科学与技术/" + str(userinfo.get("userid")) + '_' + str(year) +  "mid.docx"
            sql = "replace coursetemplates (coursetemplatesid,userid,major,coursepath,year,wordpath)values({coursetemplatesid}," \
                  "{userid},'{major}','{coursepath}',{year},'{wordpath}')".format(coursetemplatesid=coursetemplatedictinfo.get('coursetemplatesid'), userid=userinfo.get("userid"),
                major=userinfo.get('major'), coursepath=coursepath, year=int(year),wordpath=wordpath
            )
        else:
            coursepath = self.coursepath + str(userinfo.get("userid")) + '_' + str(year) + userinfo.get(
                "major") + "course" + ".xlsx"
            wordpath = "d:/word/excleapp/word/template/湘南学院/计算机科学与技术/" + str(userinfo.get("userid")) + '_' + str(
                year) + "mid.docx"
            sql = "replace coursetemplates (userid,major,coursepath,year,wordpath)values({userid},'{major}','{coursepath}',{year},'{wordpath}')".format(
                userid=userinfo.get("userid"), major=userinfo.get('major'), coursepath=coursepath, year=int(year),wordpath=wordpath
            )
        print(sql)
        self.wdsql.insert(sql)

    def updateusermajor(self, userid, major):
        sql = "update user set major= '{a}' where userid = {b}".format(b=userid, a=major)
        self.wdsql.update(sql)

    def createtbcourses(self, data=[]):
        sqlcreatetbuser = "create table if not exists user (userid int(10) auto_increment primary key,name char(20) not null," \
                          "password char(30) default '123456',major char(30))DEFAULT CHARSET=utf8;"
        self.wdsql.create(sqlcreatetbuser)
        tmp = self.templatesname
        sqlcreatetbtemplates = "create table if not exists templates (templatesid int(10) auto_increment primary key," \
                               "major char(30)," + "{} TEXT default null," * (len(self.templatesname) - 1) + \
                               "{} TEXT default null)DEFAULT CHARSET=utf8;"
        sqlcreatecoursetemplates = "create table if not exists coursetemplates (coursetemplatesid int (10) auto_increment primary key," \
                                   "userid int(10),major char(30),coursepath TEXT,year int(10),wordpath TEXT)default charset=utf8;"
        self.wdsql.create(sqlcreatecoursetemplates)

        sqlcreateschool = "create table if not exists school(schoolid int(10) auto_increment primary key,userid int(10),mycollege char(30),college char(30));"
        self.wdsql.create(sqlcreateschool)

        sqlcreateMatrix = "create table if not exists matrix(id int(10) auto_increment primary key, classname varchar(50) not null," \
                          "Content varchar(50)not null,year int (10) not null,userid int(10) not null)default charset=utf8;"
        self.wdsql.create(sqlcreateMatrix)

        sqlcreatetbtemplates = sqlcreatetbtemplates.format(tmp[0], tmp[1], tmp[2], tmp[3], tmp[4], tmp[5])
        self.wdsql.create(sqlcreatetbtemplates)
        for i in range(len(self.coursestileyuan)):
            sqlcreatetb = "create table if not exists " + self.stname[
                i] + "(major char(30),%s int(10) auto_increment primary key," + \
                          "%s char(30) default null," * 2 + "%s float(10) default 0," * (self.intcol[i]) + \
                          "%s char(30) default null," * (len(data[i]) - (4 + self.intcol[i])) + \
                          "%s char(50) default null)DEFAULT CHARSET=utf8;"
            self.wdsql.createone(sqlcreatetb, data[i])

    def selectcoursetemplates(self,year):
        user = self.selectuserdictinfo(self.userid)
        sql = "select * from coursetemplates where userid = {userid} and major = '{major}' and year={year}".format(
            userid=user.get("userid"), major=user.get('major'),year=year)
        coursetemplatesdictinfo = self.wdsql.get_onedict(sql)
        if coursetemplatesdictinfo == None:
            return -1
        return coursetemplatesdictinfo
    def selectcoursetemplates_a(self):
        # 查找所有信息
        user = self.selectuserdictinfo(self.userid)
        sql = "select * from coursetemplates where userid = {userid} and major = '{major}' ".format(
            userid=user.get("userid"), major=user.get('major'))
        coursetemplatesdictinfo = self.wdsql.get_onedict(sql)
        if coursetemplatesdictinfo == None:
            return -1
        return coursetemplatesdictinfo

    def selectcoursetemplates_id(self,id):
        # 通过id找path
        sql = "select coursepath from coursetemplates where coursetemplatesid = {id} ;".format(id = id)
        coursetemplatesdictinfo = self.wdsql.get_onedict(sql)
        if coursetemplatesdictinfo == None:
            return -1
        return coursetemplatesdictinfo

    def selectcoursetemplates_year(self,id):
        # 通过year找path
        sql = "select year from coursetemplates where coursetemplatesid = {id} ;".format(id=id)
        year = self.wdsql.get_onedict(sql)
        if year == None:
            return -1
        return year

    def selectmajorfromcourseslist(self):
        sql = "select major from 通识课 group by major"
        majors = self.wdsql.get_all(sql)
        if majors == None:
            return -1
        tmpall = []
        for i in range(len(self.processingget_allresult(majors))):
            tmp = dict(id=i + 1, major=self.processingget_allresult(majors)[i])
            tmpall.append(tmp)
        return tmpall
    def selectcoursetemplates_path(self):
        sql = "select coursetemplatesid,year,coursepath,wordpath from coursetemplates "
        path = self.wdsql.get_all(sql)
        if path == None:
            return -1
        return path

    def selectmatrix_again(self,year,classname):
        sql = "select Content from matrix where year = {year} and classname = '{classname}' and userid = {userid}".format(
            year=year,classname=classname,userid = self.userid)
        userinfo = self.wdsql.get_onedict(sql)
        if userinfo == None:
            return -1
        return userinfo

    def selectmatrix_year(self, year):
        sql = "select * from matrix where year = {year} and userid = {userid}".format(
            year=year, userid=self.userid)
        userinfo = self.wdsql.get_all(sql)
        if userinfo == None:
            return -1
        tmpall = []
        for i in range(len(userinfo)):
            tmp = dict(content=userinfo[i][2], classname=userinfo[i][1])
            tmpall.append(tmp)
        return tmpall


    def selectcoursetemplatesslist(self):
        user = self.selectuserdictinfo(self.userid)
        sql = "select major from coursetemplates where userid = {userid}".format(
            userid=user.get("userid"))
        majors = self.wdsql.get_all(sql)
        if majors == None:
            return -1
        tmpall = []
        for i in range(len(self.processingget_allresult(majors))):
            tmp = dict(id=i + 1, major=self.processingget_allresult(majors)[i])
            tmpall.append(tmp)
        return tmpall

    def selectuserdictinfo(self, userid):
        sql = "select * from user where userid={}".format(userid)
        userinfo = self.wdsql.get_onedict(sql)
        if userinfo == None:
            return -1
        return userinfo

    def selecttemplatesdict(self, major):
        sql = "select *from templates where major='{major}'".format(major=major)
        print(sql)
        templatesdictinfo = self.wdsql.get_onedict(sql)
        if templatesdictinfo == None:
            templatesdictinfo = dict.fromkeys(self.templatesname, "")
            return templatesdictinfo
        return templatesdictinfo

    def selecttemplatesdictinfo(self, major):
        sql = "select *from templates where major='{major}'".format(major=major)
        print(sql)
        templatesdictinfo = self.wdsql.get_onedict(sql)
        if templatesdictinfo == None:
            return -1
        return templatesdictinfo

    def selectuserlistinfo(self, userid):
        sql = self.cur.mogrify("select * from user where userid=%s", userid)
        userinfo = self.wdsql.get_all(sql)
        if userinfo == ():
            return -1
        tmp = []
        for i in userinfo:
            tmp.extend(i)
        userinfo = tmp
        self.LoadUDictUserInfo(userinfo)
        return self.dictuserinfo

    def selectmajorcourses(self, major):
        tmpall = []
        for course in self.stname:
            sql = "select* from {c} where major='{m}'".format(c=course, m=major)
            tmp = []
            if self.wdsql.get_one(sql) == () or self.wdsql.get_one(sql) == None:
                return -1
            for i, data in enumerate(self.wdsql.get_all(sql)):
                data = list(data[2:])
                data.insert(0, i + 1)
                tmp.append(data)
            tmpall.append(tmp)
        return tmpall

    def selectmajorcoursesyuan(self, major, courses):
        tmpall = []
        for i, course in enumerate(self.stname):
            sql = "select* from {c} where major='{m}'".format(c=course, m=major)
            tmp = []
            if self.wdsql.get_one(sql) == () or self.wdsql.get_one(sql) == None:
                return -1
            for j, data in enumerate(self.wdsql.get_all(sql)):
                tmpcourse = list(courses[i][j][1:])
                tmpcourse.insert(0, data[1])
                tmp.append(tmpcourse)
            tmpall.append(tmp)
        return tmpall

    def cleartb(self):
        tmp = []
        for table in wd.wdsql.get_all('show tables'):
            tmp.extend(table)
        # tmp = copy.deepcopy(self.stname)
        # tmp.insert(0, 'user')
        # for i in tmp:
        for i in self.stname:
            sql = "drop table %s" % i
            sql = re.sub('\'', "", sql)
            self.wdsql.delete(sql)


if __name__ == '__main__':
    wd = DbUse()
    wd.initdata()
    # wd.LoadCourse(0, 123, 'admin', '123456', "计算机科学与技术")
    # wd.LoadCourse(0, 123, 'admin', '123456', "物联网")
    # wd.insertuser(123, 'admin', '123456', '计算机科学与技术')
    print(wd.selectuserdictinfo(123))
    # wd.updateusermajor(2,"物联网")
    wd.cleartb()
    print(wd.selectmajorfromcourseslist())
    dbmajor = wd.selectmajorfromcourseslist()

